﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.IO;
using System.Data;

namespace Runda.EShop.Common.Utility
{
    public class ExcelEPPlusEngine : IDisposable
    {
        #region Property and Field

        private ExcelPackage _excelworkpackage;
        private ExcelWorkbook _excelworkbook;
        private ExcelWorksheet _excelworksheet;
        private ExcelRangeBase _excelworkrange;

        #endregion

        #region Constructor

        public ExcelEPPlusEngine()
        {
        }

        #endregion

        #region Package Methods

        public void Clone(string sourcePath, string targetPath)
        {
            //查验sourcePath的有效性
            if (!File.Exists(sourcePath))
            {
                throw new FileNotFoundException("The specified excel file can not be found.", sourcePath);
            }

            //查验targetPath的有效性
            if (File.Exists(targetPath))
            {
                throw new FileNotFoundException("The specified excel file can not be found.", targetPath);
            }


            try
            {
                File.Copy(sourcePath, targetPath);
                System.IO.File.SetAttributes(targetPath, System.IO.FileAttributes.Normal);
            }
            catch (Exception ex)
            {
                throw new Exception("Can not wait the EXCEL instance status ready.", ex);
            }
        }

        public void Open(string filepath)
        {
            FileInfo fileinfo = new FileInfo(filepath);
            _excelworkpackage = new ExcelPackage(fileinfo);

            _excelworkbook = _excelworkpackage.Workbook;
            _excelworksheet = _excelworkbook.Worksheets[1];
        }

        public void New(string SheetName)
        {
            _excelworkpackage = new ExcelPackage();
            _excelworkbook = _excelworkpackage.Workbook;
            _excelworksheet = _excelworkbook.Worksheets.Add(SheetName);
        }

        public void Save()
        {
            _excelworkpackage.Save();
        }

        public void SaveAs(string FileName)
        {
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=" + FileName);
            System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            System.Web.HttpContext.Current.Response.BinaryWrite(_excelworkpackage.GetAsByteArray());
            //_excelworkpackage.SaveAs(Response.OutputStream);    第二种方式        
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        } 

        #endregion

        #region WorkBook Methods

        #endregion

        #region WorkSheet Methods

        public void SetWorkSheet(string sheetName)
        {
            _excelworksheet = _excelworkbook.Worksheets[sheetName];
        }

        public void SetNameDefine(string Name)
        {
            _excelworksheet.Names.Add(Name, _excelworkrange);
        }

        public ExcelWorksheet GetCurrentWorkSheet()
        {
            return _excelworksheet;
        }

        #endregion

        #region Range Methods

        public void SetWorkRange(string Address)
        {
            _excelworkrange = _excelworksheet.Cells[Address];
        }

        public void SetWorkRange(int Row, int Col)
        {
            _excelworkrange = _excelworksheet.Cells[Row, Col];
        }

        public void SetWorkRange(int FromRow, int FromCol, int ToRow, int ToCol)
        {
            _excelworkrange = _excelworksheet.Cells[FromRow, FromCol, ToRow, ToCol];
        }

        public void SetNameWorkRange(string Name)
        {
            _excelworkrange = _excelworkbook.Names[Name]; 
        }

        public ExcelRangeBase GetNamedRange(string rangename)
        {
            if (string.IsNullOrEmpty(rangename))
                return null;
            foreach (var name in _excelworkbook.Names)
            {
                if (name.Name.Equals(rangename, StringComparison.InvariantCultureIgnoreCase))
                {
                    _excelworkrange = name.Current;
                    return _excelworkrange;
                }
            }
            return null;
        }

        public ExcelRangeBase GetCurrentWorkRange()
        {
            return _excelworkrange;
        }

        public void SetRangeValue(object value)
        {
            _excelworkrange.Value = value;
        }

        public void LoadFromDataTable(DataTable dt, bool PrintHeaders)
        {
            _excelworkrange.LoadFromDataTable(dt, PrintHeaders);
        }

        public void SetRangeMerge()
        {
            _excelworkrange.Merge = true;
        }

        public void SetRangeOffset(int RowOffSet,int ColumnOffSet)
        {
            _excelworkrange.Offset(RowOffSet, ColumnOffSet);
        }

        public object GetRangeValue()
        {
            return _excelworkrange.Value;
        }

        #endregion 

        #region Helper Methods

        public int ColumnNameToIndex(string colName)
        {
            int result = 0;
            string lcColName = colName.ToUpper();
            for (int ctr = 0; ctr < lcColName.Length; ++ctr)
                result = (result * 26) + (lcColName[ctr] - 'A' + 1);
            return result;
        }

        public string ColumnIndexToName(int colNum)
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            int cycleNum = (colNum - 1) / 26;
            int withinNum = colNum - (cycleNum * 26);
            if (cycleNum > 0)
                sb.Append((char)(cycleNum - 1 + 'A'));
            sb.Append((char)(withinNum - 1 + 'A'));
            return sb.ToString();
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            _excelworkrange.Dispose();
            _excelworkpackage.Dispose();
        }

        #endregion
    }

}